package com.ztesoft.report.backstage.job;

import com.alibaba.fastjson.JSON;
import com.ztesoft.report.backstage.dao.ReportDataCreateDao;
import com.ztesoft.report.dao.ReportDownloadDao;
import com.ztesoft.report.dao.SysSqlDao;
import com.ztesoft.report.entity.Datasource;
import com.ztesoft.report.entity.ReportData;
import com.ztesoft.report.entity.ServiceInterface;
import com.ztesoft.report.entity.SysSql;
import com.ztesoft.report.exception.RException;
import com.ztesoft.report.service.DataService;
import com.ztesoft.report.service.DatasourceService;
import com.ztesoft.report.service.ServiceInterfaceService;
import com.ztesoft.report.service.SysSqlService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;


@Slf4j
@Component
@Scope("prototype")
public class ReportDataCreateJob {
    @Autowired
    ReportDataCreateDao reportDataCreateDao;

    @Autowired
    DataService dataService;

    @Autowired
    ReportDownloadDao reportDownloadDao;

    @Autowired
    ServiceInterfaceService serviceInterfaceService;

    @Autowired
    DatasourceService datasourceService;

    @Autowired
    SysSqlDao sysSqlDao;

    @Value("${spring.datasource.url}")
    String url;

    public void run(ServiceInterface serviceInterface) {
        log.info("开始生成数据....");
        //考虑到有可能是合并发布，这里取出reportId作判断
        if (serviceInterface.getReportId().contains(",")) {
            String[] reportIds = serviceInterface.getReportId().split(",");
            //每一个reportId,都去执行他对应的数据生成逻辑
            for (String reportId:reportIds) {
                ServiceInterface newServiceInterface = new ServiceInterface();
                newServiceInterface.setId(serviceInterface.getId());
                newServiceInterface.setReportId(reportId);
                this.createDate(newServiceInterface,serviceInterface.getReportId());
            }
        } else {
            this.createDate(serviceInterface,serviceInterface.getReportId());
        }

    }

    /**
     * 整个数据梳理封装成一个方法
     * ids 用来 最后更新时 使用，不能让更新时，显示单个reportID
     */
    private void createDate(ServiceInterface serviceInterface,String ids){
        ReportData reportData = reportDataCreateDao.selectOneReportData(serviceInterface);
        if (reportData != null) {
            boolean flag = true ;
            String sqls = reportData.getSqls();
            String params = reportData.getParams();
            List<Map<String, String>> doneSqls = null;
            try {
                doneSqls = handleSql(sqls, params);
            } catch (Exception e) {
                log.error("",e);
                flag = false;
                this.updateServiceInterfaceStatus(serviceInterface,serviceInterface.getReportId(),"00X","解析Sql中出错："+e.getMessage());
            }
            if (flag) {
                List<Connection> conns = null;
                try {
                    conns = dataService.executeSql(doneSqls);
                } catch (Exception e) {
                    log.error("", e);
                    flag = false;
                    this.updateServiceInterfaceStatus(serviceInterface, serviceInterface.getReportId(), "00X", "执行Sql集出错：" + e.getMessage());
                }
                if (flag) {
 /*                   for (Connection conn : conns) {
                        if (conn != null) {
                            try {
                                conn.commit();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            } finally {
                                try {
                                    conn.close();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }*/
                    this.updateServiceInterfaceStatus(serviceInterface, ids, "00E", "");
                }
            }
        } else {
            this.updateServiceInterfaceStatus(serviceInterface,ids,"00E", "");
        }
    }

    /**
     * 更新serviceInterface状态
     * @param serviceInterface
     * @param status
     * @param errormsg
     */
    private void updateServiceInterfaceStatus(ServiceInterface serviceInterface,String reportIds,String status,String errormsg) {
        if (errormsg != null && errormsg != "") {
            serviceInterface.setErrorMsg(errormsg);
        }
        serviceInterface.setStatus(status);
        serviceInterface.setReportId(reportIds);
        serviceInterface.setUpdateTime(new Date());
        serviceInterfaceService.updateServiceInterface(serviceInterface);
    }

    /**
     * @param sqls         json类型字符串，sql集
     * @param params       json类型字符串，参数集
     * @return 处理好的sql集合，可以直接执行 sql 可以执行sql ，dataSourceId 为对应数据源
     */
    private List<Map<String, String>> handleSql( String sqls, String params) {
        List<Map<String, String>> doneSqls = new LinkedList<>();
        List<Map<String, Object>> sqlList = JSON.parseObject(sqls, List.class);
        List<Map<String, String>> paramlist = JSON.parseObject(params, List.class);
        for (Map<String, Object> mapSql : sqlList) {
            String sql = mapSql.get("value").toString();
            String dsID = mapSql.get("dataSourceId").toString();
            //因为所有去字典表处理的都是基于当前数据库，所以这里我们拿到当前数据库库ID传进去做处理
            Datasource localDs = new Datasource();
            localDs.setJdbcUrl(url);
            List<Datasource> datasources = datasourceService.selectByJdbc(url);
            if (datasources == null || datasources.size() < 1) {
                throw new RException("未取到当前运行的数据源");
            }
            String dictDsId = datasources.get(0).getId().toString();
            //设置一个处理{{}}的次数，防止因为异常无法替换而造成的死循环
            int count = 0;
            while (sql.contains("{{")) {
                if (count > 100) {
                    throw new RException("{{}}替换异常,请检查SQL");
                }
                log.info("处理前SQL："+sql);
                sql = cleanSql(sql, dictDsId, paramlist);
                log.info("处理后SQL："+sql);
                count++;
            }
            Map<String, String> sqlAndDsID = new HashMap<>();
            sqlAndDsID.put("sql",sql);
            sqlAndDsID.put("dataSourceId",dsID);
            doneSqls.add(sqlAndDsID);
        }
        return doneSqls;
    }

    /**
     * 替换掉所有{{}}
     * @return sql
     */
    private String cleanSql(String sql, String dataSourceID, List<Map<String, String>> paramlist) {
        //获取{{key}},这个key,然后去参数List找到对应的做相应处理
        String key = sql.substring(sql.indexOf("{{") + 2, sql.indexOf("}}")).trim();
        log.info("需要处理{{key}}中这个key为："+key);
        for (Map<String, String> mapParams : paramlist) {
            if (key.equals(mapParams.get("key"))) {
                //根据type的3种类型做出不同的处理
                switch (mapParams.get("type")) {
                    case "1":
                        //替换时候注意加上单引号,
                        sql = sql.replace("{{" + key + "}}", mapParams.get("value"));
                        break;
                    case "2":
                        SysSql sysSql2 = new SysSql();
                        sysSql2.setSqlCode(mapParams.get("dataCode"));
                        SysSql sysSqlResult2 = sysSqlDao.getSelectListBySqlCode(sysSql2);
                        dataSourceID = sysSqlResult2.getDatasourceId().toString();
                        List<Map> listType2 = dataService.executeQuerySql(dataSourceID,
                                "select value from SYS_DICT_ITEM where dict_code= '" + mapParams.get("dataCode") + "' and name = '" + mapParams.get("value") + "'");
                        if (listType2 != null && listType2.size() > 0) {
                            sql = sql.replace("{{" + key + "}}", listType2.get(0).get("VALUE").toString());
                        }
                        break;
                    case "3":
                        SysSql sysSql3 = new SysSql();
                        sysSql3.setSqlCode(mapParams.get("dataCode"));
                        SysSql sysSqlResult3 = sysSqlDao.getSelectListBySqlCode(sysSql3);
                        dataSourceID = sysSqlResult3.getDatasourceId().toString();
                        List<Map> listType3 = dataService.executeQuerySql(dataSourceID,
                                "select sql_text,datasource_id from SYS_SQL where sql_code = '" + mapParams.get("dataCode") + "'");
                        if (listType3 != null && listType3.size() > 0) {
                            String text = listType3.get(0).get("SQL_TEXT").toString();
                            List<Map> textList = dataService.executeQuerySql(dataSourceID, text);
                            if (textList != null && textList.size() > 0) {
                                //替换，比如{{yyyyMM}} ---->> 201801 这里注意没加引号 直接替换，如果到时候有需求再优化代码
                                sql = sql.replace("{{" + key + "}}",  textList.get(0).get(mapParams.get("value")).toString());

                            }
                        }
                }
            }
        }
        return sql;
    }


}
